﻿using LOG;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAO.DAOUtils
{
    public class SQLUtils
    {
        private static SqlConnection _connection = new SqlConnection(Configuration.ConnectionString);

        private static SqlCommand CreateCommand(String procName,
            ref List<Parameter> parameters)
        {
            SqlCommand command = new SqlCommand(procName, _connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            if (parameters != null)
                if (parameters.Count > 0)
                {
                    foreach (var parameter in parameters)
                    {
                        var param = new SqlParameter(parameter.Name, parameter.Value);
                        command.Parameters.Add(param);
                    }
                }

            return command;
        }

        public static bool SetConnectionString(String connectionString)
        {
            bool result = true;

            _connection.ConnectionString = connectionString;

            return result;
        }

        public static bool ExcuteNoneQuery(String procName,
            ref List<Parameter> parameters, ref Parameter outputObject)
        {

            _connection.Open();
            try
            {
                SqlCommand command = CreateCommand(procName, ref parameters);
                command.Parameters.Add(new SqlParameter(outputObject.Name, outputObject.Value));
                command.Parameters[outputObject.Name].Direction = ParameterDirection.Output;
                command.Parameters[outputObject.Name].Size = 50;

                command.ExecuteNonQuery();
                outputObject.Value = command.Parameters[outputObject.Name].Value;
            }
            catch (Exception ex)
            {
                Logger.SetMessage("SQLUtil.ExcuteNonQuery: " + ex.Message);
                return false;
            }
            finally
            { 
                _connection.Close();
            }

            return true;
        }

        public static bool ExcuteNoneQuery(String procName,
            ref List<Parameter> parameters)
        {
            bool result = true;

            _connection.Open();
            try
            {
                SqlCommand command = CreateCommand(procName, ref parameters);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.SetMessage("SQLUtil.ExcuteNoneQuery: " + ex.Message);
                result = false;
            }
            finally
            {
                _connection.Close();
            }

            return result;
        }

        public static DataTable ExcuteQuery(String procName,
            ref List<Parameter> parameters)
        {
            DataTable result = null;

            _connection.Open();
            try
            {
                SqlCommand command = CreateCommand(procName, ref parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(result);
            }
            catch (Exception ex)
            {
                Logger.SetMessage("SQLUtil.ExcuteQuery: " + ex.Message);
            }
            finally
            {
                _connection.Close();
            }

            return result;
        }
    }
}
